﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DAO
{
    public class SqlHelper
    {
        public static string ConnectString = "";
        private const string DATA_SOURCE = "Data Source";
        private const string INITIAL = "Initial Catalog";
        private const string PERSIT = "Persist Security Info";
        private const string USER_ID = "User ID";
        private const string PASS = "Password";
        public static string IMAGE_FOLDER = "Image Folder";
        public SqlHelper()
        {
            ConnectString = getConnectionString();
        }
        public static string getConnectionString()
        {
            string connectionString = "";
            connectionString += DATA_SOURCE + " = " + readConfigFile(DATA_SOURCE) + ";";
            connectionString += INITIAL + " = " + readConfigFile(INITIAL) + ";";
            connectionString += PERSIT + " = " + readConfigFile(PERSIT) + ";";
            connectionString += USER_ID + " = " + readConfigFile(USER_ID) + ";";
            connectionString += PASS + " = " + readConfigFile(PASS) + ";";
            return connectionString;
        }
        public static string readConfigFile(string key)
        {
            StreamReader file = new StreamReader(System.AppDomain.CurrentDomain.BaseDirectory + @"\config.ini");
            string input = null;
            string id;
            string value;
            int index;
            while ((input = file.ReadLine()) != null)
            {
                if (!input.Contains("************"))
                {
                    index = input.IndexOf("=");
                    if (index > -1)
                    {
                        id = input.Substring(0, index).Trim();
                        value = input.Substring(index + 1).Trim();
                        if (id != null && id.Equals(key))
                        {
                            return value;
                        }
                    }
                }
            }
            file.Close();
            return null;
        }


        public static bool CheckConnection()
        {
            ConnectString = getConnectionString();
            Console.WriteLine(ConnectString);
            if (String.IsNullOrEmpty(ConnectString))
            {
                return false;
            }
            SqlConnection con = new SqlConnection(ConnectString);
            try
            {
                con.Open();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        public static DataTable getDatasetForReport(string sql,
            CommandType commandType,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);

            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.CommandType = commandType;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null  ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }

                SqlDataAdapter dad = new SqlDataAdapter(com);
                DataSet dst = new DataSet();
                dad.Fill(dst);
                return dst.Tables[0];
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        /*
         *Execute Select store procedure
         *@return: dataTable 
         */
        public static DataTable ExecuteDataTable(
            string sql,
            CommandType commandType,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);

            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.CommandType = commandType;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }

                SqlDataAdapter dad = new SqlDataAdapter(com);
                DataSet dst = new DataSet();
                dad.Fill(dst);
                DataTable table = dst.Tables[0];
                return dst.Tables[0];

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        /*
         * @author: Nguyen Van Canh
         * @param: No
         * @return SysDate
         * Lấy ngày giờ của server
         */
        public static DateTime getSysDate()
        {
            SqlConnection con = new SqlConnection(ConnectString);
            DateTime retunvalue = DateTime.MinValue;
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand("spGetSysDate", con);
                com.CommandType = CommandType.StoredProcedure;
                SqlParameter retval = com.Parameters.Add("@SYSDATE", SqlDbType.DateTime);
                retval.Direction = ParameterDirection.Output;
                com.ExecuteReader();
                retunvalue = DateTime.Parse(retval.Value.ToString());
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return DateTime.MinValue;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        /*
         * @return true: database has been changed
         */ 
        public static bool checkOptimisticLog(string sql,
            CommandType commandType,
            params object[] pars)
        {
              SqlConnection con = new SqlConnection(ConnectString);
              bool retunvalue = true;
              try
              {
                  con.Open();
                  SqlCommand com = new SqlCommand(sql, con);
                  com.CommandType = commandType;
                  for (int i = 0; i < pars.Length; i += 2)
                  {
                      SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                      com.Parameters.Add(par);
                  }
                  SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.Bit);
                  retval.Direction = ParameterDirection.Output;
                  com.ExecuteReader();
                  retunvalue = bool.Parse(retval.Value.ToString());
                  return retunvalue;
              }
              catch (Exception e)
              {
                  Console.WriteLine(e);
                  return false;
              }
              finally
              {
                  con.Close();
                  con.Dispose();
              }
        }
        public static bool getBoolean(string storeProcedure,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            bool retunvalue = true;
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(storeProcedure, con);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }
                SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.Bit);
                retval.Direction = ParameterDirection.Output;
                com.ExecuteReader();
                retunvalue = bool.Parse(retval.Value.ToString());
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        public static float getFloat(string storeProcedure,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            float retunvalue = -1;
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(storeProcedure, con);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }
                SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.Float);
                retval.Direction = ParameterDirection.Output;
                com.ExecuteReader();
                retunvalue = float.Parse(retval.Value.ToString());
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        public static string getString(string storeProcedure,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            string retunvalue = "";
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(storeProcedure, con);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }
                SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.NVarChar);
                retval.Size = 10000;
                retval.Direction = ParameterDirection.Output;
                com.ExecuteScalar();
                retunvalue = retval.Value.ToString().Trim();
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        public static string getStringWithTran(string storeProcedure, SqlTransaction sqlTransaction, SqlConnection con,
            params object[] pars)
        {
            string retunvalue = "";
            try
            {
                SqlCommand com = new SqlCommand(storeProcedure, con, sqlTransaction);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                    com.Parameters.Add(par);
                }
                SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.NVarChar);
                retval.Size = 10000;
                retval.Direction = ParameterDirection.Output;
                com.ExecuteScalar();
                retunvalue = retval.Value.ToString().Trim();
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
            }
        }

        public static int getInt(string storeProcedure,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            int retunvalue = -1;
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(storeProcedure, con);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pars.Length; i += 2)
                {
                    SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i+1] == null ? System.DBNull.Value : pars[i+1]);
                    com.Parameters.Add(par);
                }
                SqlParameter retval = com.Parameters.Add("@retval", SqlDbType.Int);
                retval.Direction = ParameterDirection.ReturnValue;
                com.ExecuteReader();
                retunvalue = int.Parse(retval.Value.ToString());
                return retunvalue;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return -1;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }


        public static bool ExecuteNonQuery(
            string sql,
            CommandType commandType,
            params object[] pars)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            SqlTransaction sqlTransaction;
            try
            {

                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                SqlCommand com = new SqlCommand(sql, con, sqlTransaction);
                try
                {
                    com.CommandType = commandType;
                    for (int i = 0; i < pars.Length; i += 2)
                    {
                        SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                        com.Parameters.Add(par);

                    }
                    int rowsEfect = com.ExecuteNonQuery();
                    if (rowsEfect != 0)
                    {
                        sqlTransaction.Commit();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
        public static bool ExecuteNonQueryWithTranSaction(
            string sql,SqlTransaction sqlTransaction,SqlConnection con,
            params object[] pars)
        {
            try
            {
                SqlCommand com = new SqlCommand(sql, con, sqlTransaction);
                try
                {
                    com.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < pars.Length; i += 2)
                    {
                        SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1] == null ? System.DBNull.Value : pars[i + 1]);
                        com.Parameters.Add(par);

                    }
                    int rowsEfect = com.ExecuteNonQuery();
                    if (rowsEfect != 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
    }
}
